import mysql.connector
import traceback
import cx_Oracle as oracle
from coordTransform_utils import bd09_to_wgs84
import logUtil
import fileUtil

db_url = 'dev_ksh/dev_ksh@192.168.0.85/dg110dev_80'

def batchSaveToDb(poiDict, defaultTag = None):
    if len(poiDict) == 0:
        return
    sqlParams = []
    conn = oracle.connect(db_url)
    try:
        for item in poiDict:
            tag_arr = [defaultTag]
            if 'tag' in item['detail_info']:
                tag_arr = item['detail_info']['tag'].split(";")
            tag = tag_arr[0]
            tag2 = None
            if len(tag_arr) >= 2:
                tag2 = tag_arr[1]
            original_lng = item['location']['lng']
            original_lat = item['location']['lat']
            wgs_coord = bd09_to_wgs84(original_lng, original_lat)
            telephone = None
            if 'telephone' in item:
                telephone = item['telephone']
            address = item['address'] if 'address' in item else None
            sqlParams.append([item['uid'], item['name'], wgs_coord[0], wgs_coord[1], address,
                            item['province'], item['city'], item['area'], tag, tag2, original_lng, original_lat, telephone])
        cursor = conn.cursor()
        cursor.prepare("insert into T_POI(id,name,lng,lat,address,province,city,area,tag,tag2,create_time,original_lng,original_lat,telephone) values(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, sysdate, :11, :12, :13)")
        cursor.executemany(None, sqlParams)
        conn.commit()
    except oracle.DatabaseError:
        conn.rollback()
       # logUtil.logger.error("数据批量(size:%d)插入失败!尝试单条插入" % len(sqlParams))
        saveToDb(sqlParams)
    except BaseException:
        fileUtil.errorData({"data":poiDict,"defaultTag":defaultTag}).save()
    finally:
        if conn != None:
            conn.close()

def saveToDb(listData):
    conn = oracle.connect(db_url)
    for data in listData:
        try:
            cursor = conn.cursor()
            cursor.execute("insert into T_POI(id,name,lng,lat,address,province,city,area,tag,tag2,create_time,original_lng,original_lat,telephone) values(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, sysdate, :11, :12, :13)", data)
            conn.commit()
        except oracle.DatabaseError as e:
            #logUtil.logger.warning('单条数据插入失败!data:%s,excepetion:%s' % (data, str(e)))
            pass
    if conn != None:
        conn.close

def batchSaveToMysql(poiDict, defaultTag):
    if len(poiDict) == 0:
        return
    sqlParams = []
    for item in poiDict:
        tag_arr = [defaultTag]
        if 'tag' in item['detail_info']:
            tag_arr = item['detail_info']['tag'].split(";")
        tag = tag_arr[0]
        tag2 = None
        if len(tag_arr) >= 2:
            tag2 = tag_arr[1]
        original_lng = item['location']['lng']
        original_lat = item['location']['lat']
        telephone = None
        if 'telephone' in item:
            telephone = item['telephone']
        sqlParams.append([item['uid'], item['name'], original_lng, original_lat, item['address'],
                          item['province'], item['city'], item['area'], tag, tag2, original_lng, original_lat, telephone])
    conn = mysql.connector.connect(
        user='root', database='testdb', password='wei123')
    try:
        cursor = conn.cursor()
        insertSql = 'insert into T_POI(id,name,lng,lat,address,province,city,area,tag,tag2,create_time,original_lng,original_lat,telephone) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, now(), %s, %s, %s)'
        cursor.executemany(insertSql, sqlParams)
        conn.commit()
    except oracle.DatabaseError:
        traceback.print_exc()
        print("sqlParams:", sqlParams)
        conn.rollback()
    conn.close()